﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel;
using System.Data.OracleClient;
using System.Data;
using System.Drawing;
using System.Windows.Forms;

namespace PDNPAS
{
    class OracleDBHelp
    {
        //string connStr = "Data Source=orcl;User ID=pdnpasuser;Password=pdnpaspwd";
        string connStr = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=210.35.88.92) (PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=orcl)));User Id=pdnpasuser;Password=pdnpaspwd";
        
        

        public void getTable(string commandText)
        {
            OracleConnection conn = new OracleConnection(this.connStr);
            MessageBox.Show(commandText);
            try
            {
                conn.Open();//打开指定的连接
                MessageBox.Show("已连接");
                OracleCommand com = conn.CreateCommand();
                //写好想执行的Sql语句
                com.CommandText = commandText;
                    //"select name from mytable where card_no='0000000002'";
                OracleDataReader odr = com.ExecuteReader();
                MessageBox.Show("已执行查询");
                //读取数据，如果返回为false的话，就说明到记录集的尾部了
                string str = "";
                while (odr.Read())
                {
                    MessageBox.Show("在读取");
                    //将读取到的值显示到定义的控件中。
                    //this.lbl.Text = odr.GetOracleString(0).ToString();
                    str += odr[0].ToString() + ";";
                    
                }
                MessageBox.Show(str);
                odr.Close();//关闭reader.这是一定要写的
            }
            catch
            {
                MessageBox.Show("erro");//如果发生异常，则提示出错
            }
            finally
            {
                conn.Close();//关闭打开的连接
            }
        
        }

        public DataTable getDataTable(string commandText)
        {
            OracleConnection conn = new OracleConnection(this.connStr);
            try
            {
                conn.Open();
                OracleDataAdapter da = new OracleDataAdapter(commandText, conn);//新建一个DataAdapter用于填充DataSet            
                DataSet ds = new DataSet();//新建一个DataSet,这里要求commandText语句中不能有where条件语句        
                da.Fill(ds);//填充DataSet            
                DataTable table = ds.Tables[0];//新建一个DataTable
                //查看表中数据的列数
                //MessageBox.Show(table.Rows.Count.ToString());
                return table;
            }
            catch(Exception e)
            {
                MessageBox.Show(e.ToString());
                return new DataTable();
            }
            finally{ conn.Close(); }
      
        }

        public static OracleConnection GetOracleConnection(string dbName, string userName, string userPwd)
        {
            OracleConnection orclConn;
            OracleConnectionStringBuilder OrclConStrB = new OracleConnectionStringBuilder();
            OrclConStrB.DataSource = dbName;
            OrclConStrB.UserID = userName;
            OrclConStrB.Password = userPwd;
            orclConn = new OracleConnection(OrclConStrB.ConnectionString);
            return orclConn;
        }

        public static DataTable GetQueryResult(OracleConnection conn, string sql)
        {
            DataTable dtable = new DataTable();
            try
            {
                conn.Open();
                OracleDataAdapter orclDadapter = new OracleDataAdapter(sql, conn);
                orclDadapter.Fill(dtable);
            }
            catch (Exception e)
            {
                Console.WriteLine("error:" + e);
            }
            finally
            {
                conn.Close();
            }
            return dtable;
        }

        //向数据库插入一条记录
        public static void InsertRecord(OracleConnection conn, string sql)
        {
            try
            {
                conn.Open();
                OracleCommand com = new OracleCommand(sql, conn);
                com.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("error:" + e);
            }
            finally
            {
                conn.Close();
            }
        }

        //修改数据库中的记录
        public static void UpdateRecord(OracleConnection conn, string sql)
        {
            try
            {
                conn.Open();
                OracleCommand com = new OracleCommand(sql, conn);
                com.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("error:" + e);
            }
            finally
            {
                conn.Close();
            }
        }

        //删除数据库中的记录
        public static void DeleteRecord(OracleConnection conn, string sql)
        {
            try
            {
                conn.Open();
                OracleCommand com = new OracleCommand(sql, conn);
                com.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("error:" + e);
            }
            finally
            {
                conn.Close();
            }
        }
    }
}
